﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Linq;
using Nova_DAL;
using System.Data.Common;
using Nova_Common;
using System.Text;
using System.Data;
using System.IO;

public partial class Storage_StorageExcel : System.Web.UI.Page
{
    Nova_DAL.DbHelper_ERP dbHelper_ERP = new DbHelper_ERP();
    Nova_DAL.DbHelper dbHelper = new DbHelper();
    string strSQL = "";
    DbCommand cmd = null;
    static DataSet ds = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //bind();
            BindDDL();
        }
    }

    protected void bind()
    {
        cmd = dbHelper.GetSqlStringCommond(getSQL());
        ds = dbHelper.ExecuteDataSet(cmd);
        gv.DataSource = ds;
        gv.DataBind();
    }

    private void BindDDL()
    {
        strSQL = @"SELECT TBP.Pos_ID,TBZ.Zone_Name  FROM TBase_Position TBP LEFT JOIN TBase_Zone TBZ ON TBP.Zone_ID = TBZ.Zone_ID WHERE TBZ.Structure=2
                    and ifDis=0";
        cmd = dbHelper.GetSqlStringCommond(strSQL);
        ddlZoneID.DataTextField = "Zone_Name";
        ddlZoneID.DataValueField = "Pos_ID";
        ddlZoneID.DataSource = dbHelper.ExecuteDataSet(cmd);
        ddlZoneID.DataBind();
        ddlZoneID.Items.Insert(0, new ListItem("自动化立体库", "0"));
        ddlZoneID.Items.Insert(0, new ListItem("请选择库区...", "1"));
        

    }

    public override void VerifyRenderingInServerForm(Control control)
    { }
    protected string getSQL()
    {
        HttpCookie WType = Request.Cookies["WType"];

        strSQL = @"Select '' +  cast(MAX( TBP.Pos_ID) as varchar) as Pos_ID,SUM(TBS.Mat_Num) Mat_Num,MAX(TBM.Mat_Name) Mat_Name,MAX(TBM.Unit_Base) Unit_Base,'' +  cast(MAX( TBM.Mat_Code) as varchar) as Mat_Code ,MAX(TBM.S01)S01,MAX(TBS.CDT) CDT
                    From TBase_Storage TBS 
                    LEFT JOIN TRel_TrayPosContainer TRT ON TBS.ContainerID=TRT.ContainerID 
                    LEFT JOIN TBase_TrayPos TBT ON TBT.Relations_ID = TRT.Relations_ID 
                    LEFT JOIN TRelations TRP ON TRP.Tray_ID = TBT.Tray_ID 
                    LEFT JOIN TBase_Position TBP ON TRP.Pos_ID = TBP.Pos_ID 
                    LEFT JOIN TBase_InterFace TBI ON TBI.Sec_Mat_ID =TBS.Sec_Mat_ID 
                    LEFT JOIN TBase_Material TBM ON TBM.Mat_Code = TBI.Mat_Code 
                     LEFT JOIN TBase_Zone TBZ ON TBP.Zone_ID = TBZ.Zone_ID
                    Where 1=1 AND (TBI.S01 = '" + WType.Value.ToString().Trim() + "' OR  'FSSC'='" + WType.Value.ToString().Trim() + "')";
        if (txt_Mat_Code.Text != "")
        {
            strSQL += " and TBM.Mat_Code like '%" + txt_Mat_Code.Text.ToString().Trim() + "%'";
        }
        if (txt_Tray_ID.Text != "")
        {
            strSQL += " AND TRP.Tray_ID like '%" + txt_Tray_ID.Text.ToString() + "%'";
        }
        if (Position1.No_Lane != "0" && Position1.No_Lane != "")
        {
            strSQL += " AND TBP.No_Lane = '" + Position1.No_Lane.ToString() + "'";
        }
        if (Position1.No_Line != "0" && Position1.No_Line != "")
        {
            strSQL += " AND TBP.No_Line = '" + Position1.No_Line.ToString() + "'";
        }
        if (Position1.No_Col != "0" && Position1.No_Col != "")
        {
            strSQL += " AND TBP.No_Col = '" + Position1.No_Col.ToString() + "'";
        }
        if (Position1.No_Layer != "0" && Position1.No_Layer != "")
        {
            strSQL += " AND TBP.No_Layer = '" + Position1.No_Layer.ToString() + "'";
        }
        if (ddlZoneID.SelectedIndex > 1)
        {
            strSQL += " AND TBP.Pos_ID='" + ddlZoneID.SelectedValue.ToString() + "'";
        }
        else if (ddlZoneID.SelectedIndex == 1)
        {
            strSQL += " AND TBZ.Structure=1 and TBZ.ifDis=0";
        }

        strSQL += " and TBP.Pos_ID != '200000000000' GROUP BY TBM.Mat_Code Order By Max(TBS.CDT) DESC";
        return strSQL;
    }

    protected void ibtn_Search_Click(object sender, ImageClickEventArgs e)
    {
        bind();
    }

    protected void ibtn_Excel_Click(object sender, ImageClickEventArgs e)
    {
        //Response.Clear();
        //Response.BufferOutput = true;
        ////设定输出的字符集(就是简体中文集)
        //Response.Charset = "GB2312";
        ////导出的文件名为score.xls
        //Response.AppendHeader("Content-Disposition", "attachment;filename=test.xls");
        ////指定的数据是否是ASCII或一种Unicode 变体
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        ////设置导出文件的格式
        //Response.ContentType = "application/ms-excel";
        ////关闭ViewState
        //EnableViewState = false;
        //System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
        //System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
        //System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);
        //gv.RenderControl(textWriter);
        ////把HTML写回浏览器
        //Response.Write(stringWriter.ToString());
        //Response.End();

        DataTable dt = ds.Tables[0];
        Response.Clear();
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.Charset = "GB2312";
        Response.AppendHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode("库存导出" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
        Response.ContentType = "application/ms-excel";

        Response.Write("货位号\t物料编码\t物料名称\t物料数量\t单位\t规格\t写入日期\t");

        Response.Write(Environment.NewLine);
        foreach (DataRow dr in dt.Rows)
        {

            StringBuilder sb = new StringBuilder();

            sb.Append(dr["Pos_ID"] + "\t" + dr["Mat_Code"] + "\t" + dr["Mat_Name"] + "\t" + dr["Mat_Num"] + "\t" + dr["Unit_Base"] + "\t" + dr["S01"] + "\t" + dr["CDT"] + "\t" );

            Response.Write(sb.ToString());
            Response.Write(Environment.NewLine);


        }


        Response.End();
    }




    protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {


        }
    }


    protected void ibtn_ERP_Click(object sender, ImageClickEventArgs e)
    {

        strSQL = @"SELECT ID FROM TB_OrgCode";
        cmd = dbHelper.GetSqlStringCommond(strSQL);
        DataSet ds_OrgCode = dbHelper.ExecuteDataSet(cmd);

        strSQL = @"SELECT MAX(TBM.MAT_CODE) AS MAT_CODE
		                    ,MAX(TBM.MAT_NAME)AS MAT_NAME
		                    ,MAX(TBM.UNIT_BASE) AS UNIT_BASE
		                    ,SUM(convert(Decimal(19,2),ISNULL( TBS.MAT_NUM,0))) AS MAT_NUM
		                    ,MAX(TBM.S06) AS S06
		                    ,MAX(TBM.S04) AS S04
		                    ,MAX(TBM.S07) AS S07
                            ,TBI.S01 AS S01
		                    ,TBI.S02 AS S02 FROM TBase_Storage TBS 
	                        LEFT JOIN TBase_Interface TBI ON TBI.SEC_MAT_ID=TBS.SEC_MAT_ID
	                        LEFT JOIN TBase_Material TBM ON TBM.MAT_CODE=TBI.MAT_CODE
	                        GROUP BY TBM.MAT_CODE,TBI.S01,TBI.S02";
        cmd = dbHelper.GetSqlStringCommond(strSQL);
        DataSet ds_All = dbHelper.ExecuteDataSet(cmd);


        strSQL = "DELETE ITEM_ONHAND";
        cmd = dbHelper_ERP.GetSqlStringCommond(strSQL);
        dbHelper_ERP.ExecuteNonQuery(cmd);


        string strErr = "";
        int iflag = 0;
        try
        {
            lab_Err.Text = "";
            for (int i = 0; i < ds_All.Tables[0].Rows.Count; i++)
            {
                strErr = ds_All.Tables[0].Rows[i]["MAT_CODE"].ToString();
                strSQL = "INSERT INTO ITEM_ONHAND(OrgCode,Mat_Code,Mat_Name,Num,UOM_Code,UOM_Name,Wh_Code,Wh_Name)values(@OrgCode,@Mat_Code,@Mat_Name,@Num,@UOM_Code,@UOM_Name,@Wh_Code,@Wh_Name)";
                cmd = dbHelper_ERP.GetSqlStringCommond(strSQL);
                dbHelper_ERP.AddInParameter(cmd, "@OrgCode", DbType.String, ds_OrgCode.Tables[0].Rows[0][0].ToString());
                dbHelper_ERP.AddInParameter(cmd, "@Mat_Code", DbType.String, ds_All.Tables[0].Rows[i]["MAT_CODE"].ToString());
                dbHelper_ERP.AddInParameter(cmd, "@Mat_Name", DbType.String, ds_All.Tables[0].Rows[i]["MAT_NAME"].ToString());
                dbHelper_ERP.AddInParameter(cmd, "@Num", DbType.String, ds_All.Tables[0].Rows[i]["MAT_NUM"].ToString());
                dbHelper_ERP.AddInParameter(cmd, "@UOM_Code", DbType.String, ds_All.Tables[0].Rows[i]["S06"].ToString());
                dbHelper_ERP.AddInParameter(cmd, "@UOM_Name", DbType.String, ds_All.Tables[0].Rows[i]["UNIT_BASE"].ToString());
                dbHelper_ERP.AddInParameter(cmd, "@Wh_Code", DbType.String, ds_All.Tables[0].Rows[i]["S01"].ToString());
                dbHelper_ERP.AddInParameter(cmd, "@Wh_Name", DbType.String, ds_All.Tables[0].Rows[i]["S02"].ToString());
                iflag = dbHelper_ERP.ExecuteNonQuery(cmd);
                if (iflag < 0)
                {
                    lab_Err.Text = ds_All.Tables[0].Rows[i]["MAT_CODE"].ToString() + "出错！";
                }
            }
        }
        catch (Exception ex)
        {
            lab_Err.Text = strErr + "出错！";
        }

        if (lab_Err.Text.ToString() == "")
        {
            lab_Err.Text = "成功！";
        }
    }

    protected void ibtn_TOALL_Click(object sender, ImageClickEventArgs e)
    {
        strSQL = @"Select '' +  cast(MAX( TBP.Pos_ID) as varchar) as Pos_ID,SUM(TBS.Mat_Num) Mat_Num,MAX(TBM.Mat_Name) Mat_Name,MAX(TBM.Unit_Base) Unit_Base,'' +  cast(MAX( TBM.Mat_Code) as varchar) as Mat_Code ,MAX(TBM.S01)S01,MAX(TBS.CDT) CDT
                    From TBase_Storage TBS 
                    LEFT JOIN TRel_TrayPosContainer TRT ON TBS.ContainerID=TRT.ContainerID 
                    LEFT JOIN TBase_TrayPos TBT ON TBT.Relations_ID = TRT.Relations_ID 
                    LEFT JOIN TRelations TRP ON TRP.Tray_ID = TBT.Tray_ID 
                    LEFT JOIN TBase_Position TBP ON TRP.Pos_ID = TBP.Pos_ID 
                    LEFT JOIN TBase_InterFace TBI ON TBI.Sec_Mat_ID =TBS.Sec_Mat_ID 
                    LEFT JOIN TBase_Material TBM ON TBM.Mat_Code = TBI.Mat_Code 
                     LEFT JOIN TBase_Zone TBZ ON TBP.Zone_ID = TBZ.Zone_ID
                    Where TBP.Pos_ID != '200000000000' GROUP BY TBM.Mat_Code Order By Max(TBS.CDT) DESC";


        cmd = dbHelper.GetSqlStringCommond(strSQL);
        DataSet ds_all = dbHelper.ExecuteDataSet(cmd);

        ds.Tables[0].Columns[0].ColumnName = "货位号";
        ds.Tables[0].Columns[1].ColumnName = "物料编码";
        ds.Tables[0].Columns[2].ColumnName = "物料名称";
        ds.Tables[0].Columns[3].ColumnName = "物料数量";
        ds.Tables[0].Columns[4].ColumnName = "单位";
        ds.Tables[0].Columns[5].ColumnName = "规格";
        ds.Tables[0].Columns[6].ColumnName = "写入日期";


        DataTable DtOut = ds_all.Tables[0];
        Response.Clear();
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.Charset = "GB2312";
        Response.AppendHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        Response.ContentType = "application/ms-excel";


        for (int i = 0; i < DtOut.Columns.Count; i++)
        {
            Response.Write(DtOut.Columns[i].ColumnName.ToString() + "\t");
        }

        Response.Write(Environment.NewLine);
        for (int i = 0; i < DtOut.Rows.Count; i++)
        {
            StringBuilder sb = new StringBuilder();
            for (int j = 0; j < DtOut.Columns.Count; j++)
            {
                sb.Append(DtOut.Rows[i][j].ToString());
                sb.Append("\t");
            }
            Response.Write(sb.ToString());
            Response.Write(Environment.NewLine);
        }
        Response.End();
    }
    protected void gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gv.PageIndex = e.NewPageIndex;
        bind();
    }
}
